import frappe
from datetime import datetime
import calendar

# 报表执行主函数
def execute(filters=None):
    # 解析月份
    month_str = filters.get("month")

    # 如果前端没有传 month，则默认用今天
    if not month_str:
        month_str = frappe.utils.today()

    month_date = datetime.strptime(month_str, "%Y-%m-%d")


    year = month_date.year
    month = month_date.month

    # 获取当月天数，例如 11 月 = 30
    last_day = calendar.monthrange(year, month)[1]

    # 月初到月末
    from_date = datetime(year, month, 1).date()
    to_date = datetime(year, month, last_day).date()

    # 生成列头
    columns = get_columns(last_day)

    # 获取报表数据
    data = get_report_data(from_date, to_date, last_day)

    return columns, data


# 生成列（物料号、物料名、1日…30日）
def get_columns(last_day):
    columns = [
        {"label": "物料号", "fieldname": "item_code", "fieldtype": "Data", "width": 120},
        {"label": "物料名", "fieldname": "item_name", "fieldtype": "Data", "width": 160}
    ]

    # 生成 1~30(或31) 号的日期列
    for day in range(1, last_day + 1):
        columns.append({
            "label": f"{day}",
            "fieldname": f"day_{day}",
            "fieldtype": "data",
            "width": 110
        })

    return columns


# 核心：读取 Stock Entry + Stock Entry Detail，按天汇总 total_outgoing_value
def get_report_data(from_date, to_date, last_day):
    # 限定的 s_warehouse 条件
    valid_warehouses = ["HX02-002", "HX02-001", "HX01-001", "HX01-002", "HX01-004"]

    # 查询符合条件的 Stock Entry
    stock_entries = frappe.db.sql(
        """
        SELECT
            se.name AS se_name,
            se.posting_date,
            se.total_outgoing_value,
            sed.item_code,
            sed.item_name,
            sed.s_warehouse
        FROM `tabStock Entry` se
        JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
        WHERE
            se.posting_date BETWEEN %(from_date)s AND %(to_date)s
            AND se.stock_entry_type = 'Material Transfer'
            AND sed.s_warehouse IN %(valid_warehouses)s
            AND se.docstatus = 1
        """,
        {
            "from_date": from_date,
            "to_date": to_date,
            "valid_warehouses": tuple(valid_warehouses)
        },
        as_dict=True
    )

    # 用于 item_code → 每天金额 的结构
    result_map = {}

    for row in stock_entries:
        item_code = row.item_code
        item_name = row.item_name
        posting_day = row.posting_date.day  # 取得日期中的“几号”

        # 初始化结构
        if item_code not in result_map:
            result_map[item_code] = {
                "item_code": item_code,
                "item_name": item_name
            }
            # 初始化每天金额为 0
            for d in range(1, last_day + 1):
                result_map[item_code][f"day_{d}"] = 0

        # 累加 total_outgoing_value
        # 注意：Stock Entry 是整单金额，这里按明细行统计会导致重复，需要按需要调整
        result_map[item_code][f"day_{posting_day}"] = round(
            result_map[item_code][f"day_{posting_day}"] + row.total_outgoing_value,
            2
        )


    # 转换为列表
    return list(result_map.values())
